from pymysql import Connection
from flask import Flask,request,jsonify
from flask_cors import CORS
import requests

app = Flask(__name__)
CORS(app)

api_key =  '431f231a65a34c8c9982302a887d6e8f'
api_url_head = 'https://m96e4dr9kh.re.qweatherapi.com'
conn = Connection(
 host = 'localhost',
 port = 3306,
 user = 'root',
 password = 'root',
)
cursor = conn.cursor()
conn.select_db('Weather_database')
@app.route('/update', methods=['POST'])
def update():
    api_url = f'{api_url_head}/v7/weather/7d?location=101180601'
    headers = {
        'X-QW-Api-Key': api_key,
    }
    response = requests.get(api_url, headers=headers)
    response.raise_for_status()  # 检查请求是否成功
    data = response.json()
    daily_weather = data.get('daily', [])
    print(daily_weather)
    try:
        clear_sql = "DELETE FROM weather_astronomy"
        cursor.execute(clear_sql)
        conn.commit()
        for day in daily_weather:
            fxDate = day['fxDate']
            tempMax = day['tempMax']
            tempMin = day['tempMin']
            textDay = day['textDay']
            textNight = day['textNight']
            humidity = day['humidity']
            precip = day['precip']
            uvIndex = day['uvIndex']
            iconDay = day['iconDay']
            iconNight = day['iconNight']

            sql = """
            INSERT INTO weather_astronomy (fxDate, tempMax, tempMin, textDay, textNight, humidity, precip, uvIndex, iconDay, iconNight)
            VALUES  (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            ON DUPLICATE KEY UPDATE
            tempMax = VALUES(tempMax),
            tempMin = VALUES(tempMin),
            textDay = VALUES(textDay),
            textNight = VALUES(textNight),
            humidity = VALUES(humidity),
            precip = VALUES(precip),
            uvIndex = VALUES(uvIndex),
            iconDay = VALUES(iconDay),
            iconNight = VALUES(iconNight)
            """
            cursor.execute(sql, (fxDate, tempMax, tempMin, textDay, textNight, humidity, precip, uvIndex, iconDay, iconNight))
        conn.commit()  # 提交事务
        print('数据库更新成功')
        return jsonify({"code": 200, "msg": "数据库更新成功"})
    except Exception as e:
        print(f"数据库更新失败: {str(e)}")
        return jsonify({"code": 500, "msg": f"数据库更新失败: {str(e)}"}), 500

@app.route('/weather',methods=['POST'])
def temp():
    # 定义 SQL 查询语句，获取指定列的数据
    sql = "SELECT fxDate, WEEKDAY, tempMax, tempMin, textDay, textNight, humidity, precip, uvIndex,iconDay,iconNight FROM weather_astronomy"
    try:
        cursor.execute(sql)
        result = cursor.fetchall()
        # 获取查询结果的列名
        columns = [column[0] for column in cursor.description]
        # 初始化一个字典用于存储整理后的数据
        data = {col: [] for col in columns}
        # 遍历查询结果，将数据按列名整理到字典中
        for row in result:
            for i, col in enumerate(columns):
                data[col].append(row[i])
        print('数据获取成功')
        return jsonify({"code": 200, "msg": "数据获取成功", "data": data})
    except Exception as e:
        print(f"数据获取失败: {str(e)}")
        return jsonify({"code": 500, "msg": f"数据获取失败"}), 500

@app.route('/airQuality',methods=['POST'])
def airQuality():
    lng = 114
    lat = 32
    api_url = f'{api_url_head}/airquality/v1/current/{lat}/{lng}'
    headers = {
        'X-QW-Api-Key': api_key,
    }
    response = requests.get(api_url, headers=headers)
    response.raise_for_status()  # 检查请求是否成功
    data = response.json()
    print(data)
    indexes = data.get('indexes', [])
    for index in indexes:
        aqi = index.get('aqi')
    print(aqi)
    if data:
        print(f'空气质量获取成功：{aqi}')
        return jsonify({"code": 200, "msg": "<UNK>", "data": aqi})
    else:
        print("<UNK>")
        return jsonify({"code": 500, "msg": "<UNK>"})

@app.route('/login',methods=['POST'])
def login():
     data = request.get_json()
     username = data.get('username')
     password = data.get('password')
     print(username,password)
     sql = "SELECT * FROM users WHERE username=%s AND password=%s"
     cursor.execute(sql,(username,password))
     resultLogin = cursor.fetchone()
     if resultLogin:
         return {"code":200,"msg":"登录成功","user":username}
     else:
         return {"code":401,"msg":"用户名或密码错误"}
if __name__ == '__main__':
    app.run(debug=True)
